﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library;
using com.nm.library.Helper;
using george.Data;
using NHibernateVertica;
using VerticaAPI.model;

namespace VerticaAPI
{
    [Description("店铺节日补货系数信息")]
    public class DwShopHolidayRateHandler:BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        [MustLogin]
        [Description("店铺信息列表")]
        [ReturnType(typeof(PaperData<DwShopHolidayRateModel.NeceModel>))]
        public ApiResult GetDwShopHolidayRateList([ParamDescription("节日")] string HolidayKeys = "",
            [ParamDescription("店铺")] string ShopKeys = "",
            [ParamDescription("省份")] string ProID = "",
            [ParamDescription("城市")] string CityID = "",
            [ParamDescription("开始时间")] DateTime StartTime = default(DateTime),
            [ParamDescription("结束")] DateTime EndTime = default(DateTime),
            [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var count = ReturnTotalDwShopHolidayRate(StartTime, EndTime, HolidayKeys, ShopKeys, ProID, CityID);
                var result = PaperData<DwShopHolidayRateModel.NeceModel>.Create(count, Size, Page);
                var dt = ReturnDwShopHolidayRateList(StartTime, EndTime, Page, Size, HolidayKeys, ShopKeys, ProID, CityID);
                var list = ModelConvertHelper<DwShopHolidayRateModel.NeceModel>.ConvertToModel(dt);
                result.Items = list;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <param name="Keys"></param>
        /// <param name="ProID"></param>
        /// <param name="CityID"></param>
        /// <returns></returns>
        public DataTable ReturnDwShopHolidayRateList(DateTime StartTime,
            DateTime EndTime, int Page, int Size, string HolidayKeys , string ShopKeys,string ProID, string CityID)
        {
            try
            {
                var sql = $@"select * from(
SELECT  shr.* ,
        ROW_NUMBER() OVER ( ORDER BY shr.ShopCode, shr.GregorianDay ) AS Row
FROM    ( SELECT    *
          FROM      ndw.ndw_ShopHolidayRate
          WHERE     ExtrSource = '{UserInfo.UserExtrSource}'
                    AND CompanyID = '{UserInfo.UserCompanyId}'
        ) shr
        INNER JOIN ( SELECT *
                     FROM   ndw.ndw_Shop
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) ds ON shr.ShopCode = ds.ShopCode
                           AND shr.ExtrSource = ds.ExtrSource
                           AND shr.CompanyID = ds.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_Bas_Area
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) eba ON ds.AreaID = eba.AreaID
                           AND shr.ExtrSource = eba.ExtrSource
WHERE   shr.ExtrSource = '{UserInfo.UserExtrSource}'
        AND shr.CompanyID = '{UserInfo.UserCompanyId}'
        AND shr.GregorianDay >= '{StartTime:yyyy-MM-dd}'
        AND shr.GregorianDay <= '{EndTime:yyyy-MM-dd}'
        AND shr.ShopName LIKE '%{ShopKeys}%'
        AND shr.TypeName LIKE '%{HolidayKeys}%' 
{(string.IsNullOrEmpty(ProID) ? "" : $@" and eba.ParentID ={ProID} ")} 
{(string.IsNullOrEmpty(CityID) ? "" : $@" and ds.AreaID ={CityID} ")} ) table1 
where Row BETWEEN ({Page}-1)*{Size}+1 AND {Page}*{Size} ";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回总条数
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <param name="Keys"></param>
        /// <param name="ProID"></param>
        /// <param name="CityID"></param>
        /// <returns></returns>
        public int ReturnTotalDwShopHolidayRate(DateTime StartTime,
            DateTime EndTime, string HolidayKeys, string ShopKeys , string ProID, string CityID )
        {
            try
            {
                var sql =
                    $@"SELECT  COUNT(1)
FROM    ( SELECT    *
          FROM      ndw.ndw_ShopHolidayRate
          WHERE     ExtrSource = '{UserInfo.UserExtrSource}'
                    AND CompanyID = '{UserInfo.UserCompanyId}'
        ) shr
        INNER JOIN ( SELECT *
                     FROM   ndw.ndw_Shop
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) ds ON shr.ShopCode = ds.ShopCode
                           AND shr.ExtrSource = ds.ExtrSource
                           AND shr.CompanyID = ds.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_Bas_Area
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) eba ON ds.AreaID = eba.AreaID
                           AND shr.ExtrSource = eba.ExtrSource
WHERE   shr.ExtrSource = '{UserInfo.UserExtrSource}'
        AND shr.CompanyID = '{UserInfo.UserCompanyId}'
        AND shr.GregorianDay >= '{StartTime:yyyy-MM-dd}'
        AND shr.GregorianDay <= '{EndTime:yyyy-MM-dd}'
        AND shr.ShopName LIKE '%{ShopKeys}%'
        AND shr.TypeName LIKE '%{HolidayKeys}%'  
{(string.IsNullOrEmpty(ProID) ? "" : $@" and eba.ParentID ={ProID} ")} 
{(string.IsNullOrEmpty(CityID) ? "" : $@" and ds.AreaID ={CityID}")} ";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        [JsonType]
        [MustLogin]
        [Description("店铺信息列表")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult ModDwShopHolidayRate([ParamDescription("店铺节日补货列表")] DwShopHolidayRateModel.ModifyModel param)
        {
            try
            {
                if (param == null) return Error("参数对象为空");
                if (param.list == null) return Error("参数对象为空");
                if (param.list.Count <= 0) return Error("参数对象为空");
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sql = $@"UPDATE  ndw.ndw_ShopHolidayRate
SET     SuppleRate = {{0}}
WHERE   ShopCode = '{{1}}'
        AND GregorianDay = '{{2}}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}' ";
                var cmdList = new List<CommandInfo>();
                foreach (var item in param.list)
                {
                    var cmd = new CommandInfo()
                    {
                        CommandText =
                            string.Format(sql, param.newSuppleRate ?? item.SuppleRate, item.ShopCode,
                                DateTime.Parse(item.GregorianDay).ToString("yyyy-MM-dd")),
                    };
                    cmdList.Add(cmd);
                }
                Helper.ExecuteSqlTran(cmdList);
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }
    }
}
